"""
家庭成员相关的异步 CRUD 操作
"""


from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

from src.models.family_model import Family, FamilyMember, FamilyRole


async def get_family_member_by_id(
    db: AsyncSession, member_id: int
) -> FamilyMember | None:
    """根据 ID 获取家庭成员"""
    result = await db.execute(select(FamilyMember).where(FamilyMember.id == member_id))
    member = result.scalar_one_or_none()
    return member


async def get_family_member_by_username(
    db: AsyncSession, username: str
) -> FamilyMember | None:
    """根据用户名获取家庭成员"""
    result = await db.execute(
        select(FamilyMember).where(FamilyMember.username == username)
    )
    return result.scalar_one_or_none()


async def get_family_member_by_email(
    db: AsyncSession, email: str
) -> FamilyMember | None:
    """根据邮箱获取家庭成员"""
    result = await db.execute(select(FamilyMember).where(FamilyMember.email == email))
    return result.scalar_one_or_none()


async def get_family_members(
    db: AsyncSession, family_id: int, skip: int = 0, limit: int = 100
) -> list[FamilyMember]:
    """获取家庭成员列表"""
    query = (
        select(FamilyMember)
        .where(FamilyMember.family_id == family_id)
        .offset(skip)
        .limit(limit)
    )
    result = await db.execute(query)
    members = list(result.scalars().all())

    # 获取家庭名称并添加到每个成员
    if members:
        family_result = await db.execute(select(Family).where(Family.id == family_id))
        family = family_result.scalar_one_or_none()
        family_name = family.name if family else None

        for member in members:
            member.family_name = family_name

    return members


async def get_family_members_by_role(
    db: AsyncSession, role: FamilyRole, family_id: int
) -> list[FamilyMember]:
    """根据角色获取家庭成员"""
    query = select(FamilyMember).where(FamilyMember.role == role)
    if family_id:
        query = query.where(FamilyMember.family_id == family_id)
    result = await db.execute(query)
    return list(result.scalars().all())


async def create_family_member(
    db: AsyncSession,
    username: str,
    email: str,
    hashed_password: str,
    full_name: str,
    family_id: int,
    role: FamilyRole = FamilyRole.MEMBER,
    **kwargs,
) -> FamilyMember:
    """创建家庭成员"""
    # 先获取家庭名称
    family_result = await db.execute(select(Family).where(Family.id == family_id))
    family = family_result.scalar_one_or_none()
    family_name = family.name if family else None

    db_member = FamilyMember(
        username=username,
        email=email,
        hashed_password=hashed_password,
        full_name=full_name,
        role=role,
        family_id=family_id,
        family_name=family_name,
        **kwargs,
    )
    db.add(db_member)
    await db.commit()
    await db.refresh(db_member)

    return db_member


async def update_family_member(
    db: AsyncSession, member_id: int, family_id: int, **kwargs
) -> FamilyMember | None:
    """更新家庭成员"""
    # 获取家庭成员
    member = await get_family_member_by_id(db, member_id)
    if not member:
        return None

    # 更新字段
    for key, value in kwargs.items():
        if hasattr(member, key):
            setattr(member, key, value)

    await db.commit()
    await db.refresh(member)
    return member


async def delete_family_member(
    db: AsyncSession, member_id: int, family_id: int
) -> bool:
    """删除家庭成员"""
    # 获取家庭成员
    member = await get_family_member_by_id(db, member_id)
    if not member:
        return False

    await db.delete(member)
    await db.commit()
    return True


async def get_family_admins(db: AsyncSession, family_id: int) -> list[FamilyMember]:
    """获取家庭管理员"""
    return await get_family_members_by_role(db, FamilyRole.ADMIN, family_id)


async def is_family_admin(db: AsyncSession, member_id: int, family_id: int) -> bool:
    """检查是否为家庭管理员"""
    member = await get_family_member_by_id(db, member_id)
    return bool(
        member and member.family_id == family_id and member.role == FamilyRole.ADMIN
    )


# Family 模型的 CRUD 操作
async def get_family_by_id(db: AsyncSession, family_id: int) -> Family | None:
    """根据 ID 获取家庭"""
    result = await db.execute(select(Family).where(Family.id == family_id))
    return result.scalar_one_or_none()


async def get_family_by_name(db: AsyncSession, name: str) -> Family | None:
    """根据名称获取家庭"""
    result = await db.execute(select(Family).where(Family.name == name))
    return result.scalar_one_or_none()


async def get_families(
    db: AsyncSession, skip: int = 0, limit: int = 100
) -> list[Family]:
    """获取家庭列表"""
    query = select(Family).offset(skip).limit(limit)
    result = await db.execute(query)
    return list(result.scalars().all())


async def create_family(db: AsyncSession, name: str) -> Family:
    """创建家庭"""
    db_family = Family(name=name)
    db.add(db_family)
    await db.commit()
    await db.refresh(db_family)
    return db_family


async def update_family(db: AsyncSession, family_id: int, **kwargs) -> Family | None:
    """更新家庭"""
    # 获取家庭
    family = await get_family_by_id(db, family_id)
    if not family:
        return None

    # 更新字段
    for key, value in kwargs.items():
        if hasattr(family, key):
            setattr(family, key, value)

    await db.commit()
    await db.refresh(family)
    return family


async def delete_family(db: AsyncSession, family_id: int) -> bool:
    """删除家庭"""
    # 获取家庭
    family = await get_family_by_id(db, family_id)
    if not family:
        return False

    await db.delete(family)
    await db.commit()
    return True
